pandas library provides a flexible and high-performance "groupby" facility that enables you to slice,dice and summarize data sets. This follows a pattern known as split-apply-combine. This pattern data is first categorized into groups based on a criteria such as the indexes or values within the columns. Each group is then processed with an aggregation or transformation function, returning a set of data with transformed values or a single aggregate summary for each group. pandas then combines all of these results and presents it in a single data structure.
In [1]:
# import numpy,pandas
import numpy as np
import pandas as pd
# set some pandas options for controlling output
pd.set_option('display.notebook_repr_html',False)
pd.set_option('display.max_columns',10)
pd.set_option('display.max_rows',10)
# inline graphics
%matplotlib inline
Many data analysis problems utilize a pattern of processing data known as split-apply-combine. In this pattern, three steps are taken to analyze data:
This patttern is actually similar to the concepts in MapReduce. In MapReduce, massive sets of data, that are too big for a single computer, are divivided into pieces and dispatched to many systems to spread the load in manageable pieces (split). Each system then performs analysis on the data and calculates a result(apply). The results are then collected from each system and used for decision making (combined).
Once the data is split into groups one or more of the following three operations are applied:
The combine stage of the pattern is performed automatically by pandas, which will collect the results of the apply stage on all of the groups and construct a single merged result.
In [2]:
# load the sensors data
sensors = pd.read_csv("../../data/sensors.csv")
sensors
Out[2]:
In [3]:
# group this data by the sensor column / variable
# returns a DataFrameGroupBy object
grouped = sensors.groupby('sensor')
grouped
Out[3]:
The result of calling .groupby() on DataFrame is not the actual grouped data, but a DataFrameGroupBy object. The actual process of grouping is a deferred / lazy process in pandas, and at this point the grouping has not actually been performed.
In [4]:
# get the number of groups that this will create
grouped.ngroups
Out[4]:
In [5]:
# what are the groups that were found?
grouped.groups
Out[5]:
In [6]:
# a helper function to print the contents of the groups
def print_groups(groupobject):
# loop over all groups, printing the group name
# and group details
for name, group in groupobject:
print(name)
print(group)
# examine the content of the groups we created
print_groups(grouped)
The examination of these results gives us some insight into how pandas has performed the split that we specified. A group has been created for each distinct value in the sensors columns and has been named with that value. The group contains a DataFrame object whose content is the rows where the sensor value matched the name of the group.
In [7]:
grouped.size()
Out[7]:
In [8]:
# get the count of items in each column of each group
grouped.count()
Out[8]:
In [9]:
# get the data in one specific group
grouped.get_group('accel')
Out[9]:
In [10]:
# get the first three items in each group
grouped.head(3)
Out[10]:
In [11]:
# get the first item in each group
grouped.nth(0)
Out[11]:
In [12]:
# get the second item in each group
grouped.nth(1)
Out[12]:
In [13]:
grouped.nth(2)
Out[13]:
Grouping can also be performed on multiple columns by passing a list of column names. The following groups the data by both sensor and axis variables:
In [14]:
# group by both sensor and axis values
mcg = sensors.groupby(['sensor','axis'])
print_groups(mcg)
In [15]:
# get descriptive statistics for each
mcg.describe()
Out[15]:
By default, groups are sorted by their group name in an ascending order. If you want to prevent sorting during grouping use the sort=False option.
In [16]:
# make a copy of the data and reindex the copy
mi = sensors.copy()
mi = mi.set_index(['sensor','axis'])
mi
Out[16]:
In [17]:
# group by first level of index
mig_11 = mi.groupby(level=0)
print_groups(mig_11)
In [18]:
# group by multiple levels of the index
mig_112 = mi.groupby(level=['sensor','axis'])
print_groups(mig_112)
After the grouping is performed, we have the ability to perform either aggregate calculations on each group of data resulting in a single value from each group or to apply a transformation to each item in a group and return the combined result for each group.
We can also filter groups based on results of expressions to exclude the groups from being included in the combined results.
In [19]:
# calculate the mean for each sensor / axis
mig_112.agg(np.mean)
Out[19]:
In [20]:
# do not create an index matching the original object
sensors.groupby(['sensor','axis'],as_index=False).agg(np.mean)
Out[20]:
In [21]:
# can simply apply the agg function to the group by object
mig_112.mean()
Out[21]:
In [23]:
# apply multiple aggregation functions at once
mig_112.agg([np.sum,np.std])
Out[23]:
A different function can be applied to each column in each group by passing a Python dictionary to .agg(), where the keys of the dictionary represent the column name that the function is to be applied to and the value is the function.
In [24]:
# apply a different function to each column
mig_112.agg({'interval':len,'reading':np.mean})
Out[24]:
Aggregation can also be performed on specific columns using the [] operator on the GroupBy object.
In [25]:
# calculare the mean of the reading column
mig_112['reading'].mean()
Out[25]:
The GroupBy objects provide a .transform() method, which applies a function to each group and returns either the Series or DataFrame that has the following parameters
In [26]:
# DataFrame for example
df = pd.DataFrame({'Label':['A','C','B','A','C'],
'Values':[0,1,2,3,4],
'Values2':[5,6,7,8,9],
'Noise': ['foo','bar','baz','foobar','barbaz']})
df
Out[26]:
In [27]:
# group by label
grouped = df.groupby('Label')
print_groups(grouped)
In [28]:
# add ten to all values in all columns
grouped.transform(lambda x: x + 10)
Out[28]:
To understand what is going on more clearly, we can change the function being passed to .transform() to write some diagnostic information.
In [29]:
# a function to print the input before we are adding 10 to it
def xplus10(x):
print(x)
return x + 10
# transform using xplus10
grouped.transform(xplus10)
Out[29]:
pandas has called the transformation function nine times, one time for every column in every group (3 x 3) and passed a Series object for each combination of group / rows / column and for each of these calls, pandas stores the results and when complete, does a merge of the results back in to DataFrame that is indexed identically to the original data.
In [30]:
# sum returns existing as it is applied to each individual item
grouped.transform(lambda x:x.sum())
Out[30]:
In [31]:
# data to demonstrate replacement on NaN
df = pd.DataFrame({'Label':list("ABABAB"),
'values':(10,20,11,np.NaN,12,22)},
index = ['i1','i2','i3','i4','i5','i6'])
df
Out[31]:
In [32]:
# group by label
grouped = df.groupby('Label')
print_groups(grouped)
In [33]:
# calculate the mean of the two groups
grouped.mean()
Out[33]:
In [34]:
# use transform to fill the NaN with mean of the group
filled_NaNs = grouped.transform(lambda x: x.fillna(x.mean()))
filled_NaNs
Out[34]:
In [35]:
type(filled_NaNs)
Out[35]:
The result appears odd at a first glance because of the following reasons:
On the first two points, our original DataFrame has two columns one of which was used in the grouping process. This column is not passed to the .transform() method and in this scenario the only column processed is Values. Upon applying the .transform() method on each group, pandas merges the results which are all Series objects.
With respect to the third point, we do not know which values in which groups were changed, but we do know the index in the original data as the index labels are preserved through the process.
In [37]:
# overwrite old values with the new ones
df.values = filled_NaNs
df
Out[37]:
Another common and practical example is that of using .transform() in statistical analysis and is the process of normalizing multiple groups of data to have a mean 0 and a standard deviation of 1 also referred to as creating a normalized z score of the data.
In [39]:
# generate a rolling mean time series
np.random.seed(123456)
data = pd.Series(np.random.normal(0.5,2,365 * 3),
pd.date_range('2001-01-01', periods=365*3))
rolling = pd.rolling_mean(data,100,100).dropna()
rolling
Out[39]:
In [40]:
rolling.plot()
Out[40]:
In [42]:
# calculate mean and std by year
groupkey = lambda x:x.year
groups = rolling.groupby(groupkey)
groups.agg([np.mean,np.std])
Out[42]:
In [43]:
# normalize to the z score
zscore = lambda x: (x - x.mean()) / x.std()
normed = rolling.groupby(groupkey).transform(zscore)
normed.groupby(groupkey).agg([np.mean,np.std])
Out[43]:
In [44]:
# plot original vs normalize
compared = pd.DataFrame({'Original': rolling,'Normed':normed})
compared.plot()
Out[44]:
In [45]:
# check the distribution # within one std
# should be roughly 64.2%
normed_inlstd = normed[np.abs(normed) <= 1.0].count()
float(normed_inlstd) / len(normed)
Out[45]:
pandas GroupBy object provides a .filter() method, which can be used to make group level decisions on whether or not the entire group is included in the result after the combination. The function passed to .filter() should return True if the group is to be included in the result and False to exclude it.
In [46]:
# data for our examples
df = pd.DataFrame({'Label':list("AABBCC"),
'Values':(1,2,3,4,np.nan,8)})
df
Out[46]:
In [47]:
# drop groups with one or fewer non-NaN values
f = lambda x:x.Values.count() > 1
df.groupby('Label').filter(f)
Out[47]:
Notice that there is a subtle difference when it comes to using .filter() as compared to .translate(). The data passed to the function specified in the call to .filter() is passed to the columns specified in the .groupby( method.
In [48]:
# drop any groups with NaN values
f = lambda x: x.Values.isnull().sum() == 0
df.groupby('Label').filter(f)
Out[48]:
In [50]:
# select groups with a mean of 2.0 or greater
grouped = df.groupby('Label')
mean = grouped.mean().mean()
f = lambda x: abs(x.Values.mean() -mean) > 2.0
df.groupby('Label').filter(f)
Out[50]:
In [52]:
# replace values in a group where the # of items is <=1
f = lambda x: x.Values.count() > 1
df.groupby('Label').filter(f,dropna=False)
Out[52]:
Discretization is the process of slicing up continuous data into a set of "bins" where each bin represents a range of the continous sample and items are then placed into the appropriate bin hence the term "binning".
Discretization in pandas is performed using the pd.cut() and pd.qcut() functions.
In [53]:
# generate 10000 normal random #'s
np.random.seed(123456)
dist = np.random.normal(size=10000)
# show the mean and std
"{0} {1}".format(dist.mean(),dist.std())
Out[53]:
In [54]:
dist
Out[54]:
In [55]:
# split the data into 5 bins
bins = pd.cut(dist,5)
bins
Out[55]:
In [56]:
# show the categories in bins
bins.categories
Out[56]:
In [57]:
# demonstrate the math to calculate the bins
min = dist.min()
max = dist.max()
delta = max - min
iwidth = delta / 5
extra = delta * 0.001
intervals = np.arange(min,max*extra,iwidth)
intervals[0] -= delta*0.001
intervals
Out[57]:
In [58]:
# codes tells us which bin each item is in
bins.codes
Out[58]:
The notation for the intervals follows standard mathematical intervals where a parenthesis represents that the end is open while square brackets are closed. Closed ends include values at that exact number. By default pandas closes the right-handed side of intervals.
In [59]:
# move the closed side of the interval to the left
pd.cut(dist,5,right=False).categories
Out[59]:
In [60]:
# generate 50 ages between 6 and 45
np.random.seed(123456)
ages = np.random.randint(6,45,50)
ages
Out[60]:
In [61]:
# cut into ranges and then get descriptive stats
ranges = [6,12,18,35,50]
agebins = pd.cut(ages,ranges)
agebins.describe()
Out[61]:
In [62]:
# add names for the bins
ranges = [6,12,18,35,50]
labels = ['Youth','Young Adult','Adult','Middle Aged']
agebins = pd.cut(ages, ranges, labels=labels)
agebins.describe()
Out[62]:
Data can be sliced according to specified quantities using pd.qcut(). This is the process of placing the values into bins such that each bin has the same number of items. To do this, the ranges of the quantities must be determined during the process so that the distribution is even.
In [63]:
# cut into quantiles
# 5 bins with an equal quality of items
qbin = pd.qcut(dist,5)
# this will tell us the range of values in each quantile
qbin.describe()
Out[63]:
In [64]:
# make the quantiles at the +/- 3,2 and 1 std deviations
quantiles = [0,0.001,0.021,0.5-0.341,0.5,0.5+0.341,1.0-0.021,1.0-0.001,1.0]
qbin = pd.qcut(dist,quantiles)
# this data should be a perfect normal distribution
qbin.describe()
Out[64]: